﻿/**********************************天使物流*******************************************
* 功能描述:结算管理-新增应付费用
* 作者:石锦涛
* 时间:2022-01-10
* 
**********************************天使物流*******************************************/
using Snowflake.Core;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.Common;
using TianShiProject.Models.SettleAccounts;
using TianShiProject.Models.SettleAccounts.NewExpensesPayabs;
using Volo.Abp.EntityFrameworkCore;

namespace TianShiProject.SettleAccounts.NewExpensesPayabs
{
    public class NewExpensesAppRepository : DapperDbContextRepository, INewExpensesAppRepository
    {
        public NewExpensesAppRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        /// <summary>
        /// 显示应付费用
        /// </summary>
        /// <returns></returns>
        public async Task<PageResponse<NewExpensesPayabDto>> GetNewExpenses(string unit, string account, int? state, string handle, string times, int pageindex = 1, int pagesize = 5)
        {
            try
            {
                PageResponse<NewExpensesPayabDto> doc = new PageResponse<NewExpensesPayabDto>();

                var sql = "select Id as GId,DusinessNumbers,TheOwnerUnitls,ClearingForms,Tonnages,UnitPrices,Moneycs,SUBSTRING(CONVERT(Nvarchar(50),BusinessTimes),1,10) as BusinessTimes,ResponsiblePersonxs,Remarkus,SUBSTRING(CONVERT(Nvarchar(50), EstablishTimes), 1, 10) as EstablishTimes,CheckStatets,Collatoras,SUBSTRING(CONVERT(Nvarchar(50), CheckTimes), 1, 10) as CheckTimes,IsDelete from NewExpensesPayab where IsDelete = 0  ";
                if(!string.IsNullOrEmpty(unit))
                {
                    sql += $" and TheOwnerUnitls like '%{unit}%'";
                }
                if (!string.IsNullOrEmpty(account))
                {
                    sql += $" and ClearingForms like '%{account}%'";
                }
                if (state>0)
                {
                    sql += $" and CheckStatets = '{state}'";
                }
                if (!string.IsNullOrEmpty(handle))
                {
                    sql += $" and ResponsiblePersonxs like '%{handle}%'";
                }
                if (!string.IsNullOrEmpty(times))
                {
                    sql += $" and BusinessTimes ='{times}'";
                }
                var list = await this.QueryAsync<NewExpensesPayabDto>(sql,null);
                doc.PageTotal = list.Count();
                sql += $" order by Id offset ({pageindex}-1)*{pagesize} rows fetch next {pagesize} rows only";
                var query = await this.QueryAsync<NewExpensesPayabDto>(sql, null);
                doc.PageData = query;
                return doc;

            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<PageResponse<NewExpensesPayabDto>> DeleteNewExpenses(int Id)
        {
            try
            {
                PageResponse<NewExpensesPayabDto> doc = new PageResponse<NewExpensesPayabDto>();
                var sql = $"update  NewExpensesPayab set IsDelete=1 where Id={Id}";
                doc.Code = await this.ExecuteAsync(sql, null);
                return doc;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 应付费用修改
        /// </summary>
        /// <param name="newExpenses"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> UpdateNewExpenses(NewExpensesPayabDto newExpenses)
        {
            try
            {
                var sql = $"update NewExpensesPayab set DusinessNumbers='{newExpenses.DusinessNumbers}',TheOwnerUnitls='{newExpenses.TheOwnerUnitls}',ClearingForms='{newExpenses.ClearingForms}',Tonnages='{newExpenses.Tonnages}',UnitPrices = '{newExpenses.UnitPrices}',Moneycs = '{newExpenses.Moneycs}',BusinessTimes = '{newExpenses.BusinessTimes.ToString("yyyy/MM/dd")}',ResponsiblePersonxs = '{newExpenses.ResponsiblePersonxs}',Remarkus = '{newExpenses.Remarkus}', EstablishTimes = '{newExpenses.EstablishTimes.ToString("yyyy/MM/dd")}',CheckStatets = '{newExpenses.CheckStatets}',Collatoras = '{newExpenses.Collatoras}',CheckTimes = '{newExpenses.CheckTimes.ToString("yyyy/MM/dd")}',IsDelete = '{newExpenses.IsDelete}' where Id = '{newExpenses.GId}'";
                PageResponse<int> doc = new PageResponse<int>();
                doc.Code = await this.ExecuteAsync(sql, null);
                return doc;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 添加应付费用
        /// </summary>
        /// <param name="newExpenses"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> PostNewExpenses(NewExpensesPayabDto newExpenses)
        {
            try
            {
                //雪花算法
                var worker = new IdWorker(1, 1);
                var id = worker.NextId().ToString();

                var sql = $"insert NewExpensesPayab values('{id}','{newExpenses.TheOwnerUnitls}','{newExpenses.ClearingForms}','{newExpenses.Tonnages}','{newExpenses.UnitPrices}','{newExpenses.Moneycs}','{newExpenses.BusinessTimes.ToString("yyyy/MM/dd")}','{newExpenses.ResponsiblePersonxs}','{newExpenses.Remarkus}',GETDATE(),'{newExpenses.CheckStatets}','{newExpenses.Collatoras}',GETDATE(),'{newExpenses.IsDelete}')";
                PageResponse<int> doc = new PageResponse<int>();
                doc.Code = await this.ExecuteAsync(sql,null);
                return doc;

            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 批量逻辑删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<PageResponse<NewExpensesPayabDto>> DeleteNewExpensesMser(string Id)
        {
            try
            {
                PageResponse<NewExpensesPayabDto> doc = new PageResponse<NewExpensesPayabDto>();
                var ids = Id.Split(',');
                foreach (var item in ids)
                {
                    var shu = Convert.ToInt32(item);
                    var list = await this.QueryAsync<NewExpensesPayabDto>($"select * from NewExpensesPayab where Id={shu}");
                    if (list != null)
                    {
                        var sql = $"update NewExpensesPayab set IsDelete=1 where Id={shu} ";
                        doc.Code = await this.ExecuteAsync(sql, null);
                    }
                }
                return doc;
            }
            catch (Exception)
            {

                throw;
            }
        }

    }
}
